﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FindArbitrageConsole.Data.Model;
using MySql.Data.MySqlClient;

namespace FindArbitrageConsole.Data
{
    public class DataService : IDataService
    {
        private MySqlConnection Connection { get; set; }

        public DataService(string connectionString)
        {
            //connectionString = "SERVER=localhost;DATABASE=kef_stats;UID=root;PASSWORD=Sql2008";

            var strBuilder = new MySqlConnectionStringBuilder(connectionString)
                                 {
                                     Pooling = true, 
                                     MaximumPoolSize = 5, 
                                     Keepalive = 20
                                 };

            Connection = new MySqlConnection(strBuilder.ToString());
        }

        [Obsolete("Old func. Used by DbConnection")]
        public IEnumerable<KefData> GetAllMaxKefsDeprecate(Int32? byEventId = null)
        {
            string query = "SELECT * FROM kef_max";
            if (byEventId != null)
                query += string.Format(" WHERE event_id = {0}",byEventId);

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }

        #region Funcs only for Repositories

        public IEnumerable<KefData> GetAllMaxKefs()
        {
            const string query = "SELECT * FROM kef_max";

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }
        public IEnumerable<KefData> GetAllMaxKefsAfterUpdateTime(DateTime moreThanUpdateTime)
        {
            const string query = "SELECT * FROM kef_max WHERE update_time > @datetime";

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;
                cmd.Parameters.Add(new MySqlParameter("datetime",moreThanUpdateTime));

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }

        public IEnumerable<KefData> GetAllKefs()
        {
            const string query = "SELECT * FROM kef";

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }
        public IEnumerable<KefData> GetAllKefsAfterUpdateTime(DateTime moreThanUpdateTime)
        {
            const string query = "SELECT * FROM kef WHERE update_time > @datetime";

            var listKefs = new List<KefData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;
                cmd.Parameters.Add(new MySqlParameter("datetime", moreThanUpdateTime));

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var id = Convert.ToInt32(dataReader["id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var kefData = new KefData(id, eventId, marketId, selectionId, bookmakerId, kefValue, updateTime);
                    listKefs.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listKefs;
        }



        #endregion

        public IEnumerable<int> GetAllEvents()
        {
            const string query = "SELECT event_id FROM kef GROUP BY event_id";

            var listEvents = new List<int>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    listEvents.Add(Convert.ToInt32(dataReader["event_id"]));
                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listEvents;
        }

        public IEnumerable<ArbitrageLinkData> GetArbitrageLinks()
        {
            const string query = "SELECT arbitrage_id,kef_id FROM arbitrage_link";

            var listArbLinks = new List<ArbitrageLinkData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var arbitrageId = Convert.ToInt32(dataReader["arbitrage_id"]);
                    var kefId = Convert.ToInt32(dataReader["kef_id"]);

                    var kefData = new ArbitrageLinkData(){ ArbitrageId = arbitrageId,KefId = kefId};
                    listArbLinks.Add(kefData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listArbLinks;
        }

        public IEnumerable<ExtendedArbitrageLinkData> GetExtendedArbitrageLinks()
        {
            const string query = " SELECT arbitrage_id,kef_id,event_id,market_id,selection_id,kef_value,bookmaker_id,update_time" +
                                 " FROM arbitrage_link INNER JOIN kef on arbitrage_link.kef_id = kef.id";

            var listExtArbLinks = new List<ExtendedArbitrageLinkData>();

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;

                var dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    var arbitrageId = Convert.ToInt32(dataReader["arbitrage_id"]);
                    var kefId = Convert.ToInt32(dataReader["kef_id"]);
                    var eventId = Convert.ToInt32(dataReader["event_id"]);
                    var marketId = Convert.ToInt32(dataReader["market_id"]);
                    var selectionId = Convert.ToInt32(dataReader["selection_id"]);
                    var kefValue = Convert.ToDecimal(dataReader["kef_value"]);
                    var bookmakerId = Convert.ToInt32(dataReader["bookmaker_id"]);
                    var updateTime = Convert.ToDateTime(dataReader["update_time"]);

                    var arbLinkData = new ExtendedArbitrageLinkData()
                                      {
                                          ArbitrageId = arbitrageId, 
                                          KefId = kefId,
                                          EventId = eventId,
                                          MarketId = marketId,
                                          SelectionId = selectionId,
                                          BookmakerId = bookmakerId,
                                          KefValue = kefValue,
                                          UpdateTime = updateTime
                                      };
                    listExtArbLinks.Add(arbLinkData);

                }
                dataReader.Close();
                this.CloseConnection();
            }
            return listExtArbLinks;
        }

        public void WriteNewArbitrages(List<IEnumerable<ArbitrageLinkData>> listArbitrages, DateTime currentTime)
        {
            if (listArbitrages == null || !listArbitrages.Any())
                return;

            foreach (var arb in listArbitrages)
            {
                var arbId = CreateArbitrage(currentTime);
                arb.ToList().ForEach(l => l.ArbitrageId = arbId.Value);
                WriteArbitrage(arb);
            }

        }

        public void UpdateExistingArbitrages(IEnumerable<int> arbitrageIds, DateTime updateTime)
        {
            if (!arbitrageIds.Any())
                return;

            var query = string.Format(
                "UPDATE arbitrage SET update_time = @updateTime WHERE arbitrage.id in ({0})",
                string.Join(",", arbitrageIds)
                );

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = query;
                cmd.Parameters.Add(new MySqlParameter("updateTime", updateTime));
                cmd.ExecuteNonQuery();

                this.CloseConnection();
            }
        }

        public void DeleteIrrelevantArbitrages(DateTime currentTime)
        {
            const string queryDelete = @"   DELETE FROM arbitrage_link 
                                            WHERE EXISTS(
                                                            SELECT 1 FROM arbitrage 
                                                            WHERE arbitrage.id = arbitrage_link.arbitrage_id AND arbitrage.update_time < @currentTime
                                                        )";
            const string queryEndTime = "UPDATE arbitrage SET end_time = @currentTime WHERE end_time is null AND NOT EXISTS(select arbitrage_id from arbitrage_link where arbitrage_id = arbitrage.id)";

            const string queryCascadeDelete = @"DELETE FROM arbitrage WHERE arbitrage.update_time < @currentTime";

            if (this.OpenConnection() == true)
            {
                //Выполняем запрос на удаление зависимостей из arbitrage_link
                //var cmdDelete = Connection.CreateCommand();
                //cmdDelete.CommandText = queryDelete;
                //cmdDelete.Parameters.Add(new MySqlParameter("currentTime", currentTime));
                //var deleted = cmdDelete.ExecuteNonQuery();
                
                //Проставляем вилке Дату окончания. Проставляем тем вилкам, связей которых не находим в таблице arbitrage_link
                //var cmdEndTime = Connection.CreateCommand();
                //cmdEndTime.CommandText = queryEndTime;
                //cmdEndTime.Parameters.Add(new MySqlParameter("currentTime", currentTime));
                //cmdEndTime.ExecuteNonQuery();

                var cmdCascadeDel = Connection.CreateCommand();
                cmdCascadeDel.CommandText = queryCascadeDelete;
                cmdCascadeDel.Parameters.Add("currentTime",MySqlDbType.DateTime).Value = currentTime;
                cmdCascadeDel.ExecuteNonQuery();

                this.CloseConnection();
            }
        }

        #region Private Funcs

        private void WriteArbitrage(IEnumerable<ArbitrageLinkData> listLinks)
        {
            if (listLinks == null || !listLinks.Any())
                return;

            const string insertQueryHead = "INSERT INTO arbitrage_link(arbitrage_id,kef_id) VALUES ";

            var listValues = listLinks.Select(link => (string.Format(" ({0},{1}) ", link.ArbitrageId, link.KefId)));

            var completeQuery = insertQueryHead + string.Join(",", listValues);

            if (this.OpenConnection() == true)
            {
                var cmd = Connection.CreateCommand();
                cmd.CommandText = completeQuery;
                cmd.ExecuteNonQuery();

                this.CloseConnection();
            }
        }
        
        private Int32? CreateArbitrage(DateTime currentTime)
        {
            //string insertQuery = string.Format("INSERT INTO arbitrage(some_info) values('{0}')", someData);
            string insertQuery = string.Format("INSERT INTO arbitrage(start_time,update_time) values(@currentTime,@currentTime)");
            string queryGetId = string.Format("SELECT MAX(id) FROM arbitrage");

            Int32? arbId = null;

            if (this.OpenConnection() == true)
            {

                var cmdInsert = Connection.CreateCommand();
                cmdInsert.CommandText = insertQuery;
                cmdInsert.Parameters.Add("currentTime", MySqlDbType.DateTime).Value = currentTime;
                cmdInsert.ExecuteNonQuery();

                var cmdGetId = Connection.CreateCommand();
                cmdGetId.CommandText = queryGetId;
                arbId = Convert.ToInt32(cmdGetId.ExecuteScalar());

                this.CloseConnection();
            }

            return arbId;
        }

        #endregion

        #region Private Funcs Connection
        private bool OpenConnection()
        {
            try
            {
                Connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.

                /*
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                */
                return false;
            }
        }
        private bool CloseConnection()
        {
            try
            {
                Connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message);
                return false;
            }
        }
        #endregion
    }
}
